package cn.edu.sdyu.ie.crs.repository;

import cn.edu.sdyu.ie.crs.common.response.LabelValue;
import cn.edu.sdyu.ie.crs.domain.Assignment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;

@Repository
public interface AssignmentRepository extends JpaRepository<Assignment, Integer> {

    @Query(value = "SELECT DATE(a.execution_date) AS label, count(*) AS value"
            + " FROM assignment a"
            + " WHERE DATE(a.execution_date) BETWEEN :beginDateTime and :endDateTime"
            + " GROUP BY DATE(a.execution_date)", nativeQuery = true)
    List<LabelValue> countGroupByExecutionDateBetween(@Param("beginDateTime") LocalDate beginDateTime,
                                            @Param("endDateTime") LocalDate endDateTime);

    List<Assignment> findAssignmentsByUserAndState(String user, Integer state);

}
